package net.wanho.customerManage.dao.impl;

import net.wanho.commons.dao.impl.BaseDAOImpl;
import net.wanho.commons.exception.DAOException;
import net.wanho.commons.exception.DataAccessException;
import net.wanho.customerManage.dao.CustomerDAO;
import net.wanho.customerManage.po.Customer;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CustomerDAOImpl extends BaseDAOImpl<Customer> implements CustomerDAO {
    @Override
    public List<Customer> selectAll() throws DAOException {
        try {
            String sql = "SELECT\n" +
                    "  c.CUSTOMER_ID         customerId,\n" +
                    "  c.CUSTOMER_NAME       customerName,\n" +
                    "  c.POST_CODE           postCode,\n" +
                    "  c.CREATE_TIME         createTime,\n" +
                    "  c.UPDATE_TIME         updateTime,\n" +
                    "  c.EMPLOYEE_NUMBERS    employeeNumbers,\n" +
                    "  c.ADDRESS             address,\n" +
                    "  c.TAG                 tag,\n" +
                    "  c.REMARKS             remarks,\n" +
                    "  c.BUSUBESS_VOLUME     busubessVolume,\n" +
                    "  l.LINK_MAIN_ID        \"prinpical.linkMainId\",\n" +
                    "  l.NAME                \"prinpical.name\",\n" +
                    "  l.SEX                 \"prinpical.sex\",\n" +
                    "  l.POSITION            \"prinpical.position\",\n" +
                    "  l.NICKNAME            \"prinpical.nickname\",\n" +
                    "  l.PHONENUM            \"prinpical.phonenum\",\n" +
                    "  l.EMAIL               \"prinpical.email\",\n" +
                    "  l.QQ                  \"prinpical.qq\",\n" +
                    "  l.CREATE_TIME         \"prinpical.createTime\",\n" +
                    "  l.UPDATE_TIME         \"prinpical.updateTime\",\n" +
                    "  l.REMARKS             \"prinpical.remarks\",\n" +
                    "  l.QR_CODE             \"prinpical.qrode\",\n" +
                    "  l.CUSTOMER_ID         \"prinpical.customerId\",\n" +
                    "  bs.SOURCE_ID          \"source.sourceId\",\n" +
                    "  bs.SOURCE_NAME        \"source.sourceName\",\n" +
                    "  wf.FIELD_ID           \"field.fieldId\",\n" +
                    "  wf.FIELD_NAME         \"field.fieldName\"\n" +
                    "FROM customer c\n" +
                    "  LEFT JOIN link_man l\n" +
                    "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                    "  LEFT JOIN business_source bs\n" +
                    "    ON c.SOURCE_ID = bs.SOURCE_ID\n" +
                    "  LEFT JOIN working_field wf\n" +
                    "    ON c.FIELD_ID = wf.FIELD_ID";
            Object[] params = {};
            return super.queryForList(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public Customer findById(String id) throws DAOException {
        try {
            String sql = "SELECT\n" +
                    "  c.CUSTOMER_ID         customerId,\n" +
                    "  c.CUSTOMER_NAME       customerName,\n" +
                    "  c.POST_CODE           postCode,\n" +
                    "  c.CREATE_TIME         createTime,\n" +
                    "  c.UPDATE_TIME         updateTime,\n" +
                    "  c.EMPLOYEE_NUMBERS    employeeNumbers,\n" +
                    "  c.ADDRESS             address,\n" +
                    "  c.TAG                 tag,\n" +
                    "  c.REMARKS             remarks,\n" +
                    "  c.BUSUBESS_VOLUME     busubessVolume,\n" +
                    "  l.LINK_MAIN_ID        \"prinpical.linkMainId\",\n" +
                    "  l.NAME                \"prinpical.name\",\n" +
                    "  l.SEX                 \"prinpical.sex\",\n" +
                    "  l.POSITION            \"prinpical.position\",\n" +
                    "  l.NICKNAME            \"prinpical.nickname\",\n" +
                    "  l.PHONENUM            \"prinpical.phonenum\",\n" +
                    "  l.EMAIL               \"prinpical.email\",\n" +
                    "  l.QQ                  \"prinpical.qq\",\n" +
                    "  l.CREATE_TIME         \"prinpical.createTime\",\n" +
                    "  l.UPDATE_TIME         \"prinpical.updateTime\",\n" +
                    "  l.REMARKS             \"prinpical.remarks\",\n" +
                    "  l.QR_CODE             \"prinpical.qrode\",\n" +
                    "  l.CUSTOMER_ID         \"prinpical.customerId\",\n" +
                    "  bs.SOURCE_ID          \"source.sourceId\",\n" +
                    "  bs.SOURCE_NAME        \"source.sourceName\",\n" +
                    "  wf.FIELD_ID           \"field.fieldId\",\n" +
                    "  wf.FIELD_NAME         \"field.fieldName\"\n" +
                    "FROM customer c\n" +
                    "  LEFT JOIN link_man l\n" +
                    "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                    "  LEFT JOIN business_source bs\n" +
                    "    ON c.SOURCE_ID = bs.SOURCE_ID\n" +
                    "  LEFT JOIN working_field wf\n" +
                    "    ON c.FIELD_ID = wf.FIELD_ID\n" +
                    "WHERE c.CUSTOMER_ID = ?";
            Object[] params = {id};
            return super.queryForObject(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public boolean insert(Customer customer) throws DAOException {
        try {
            String sql = "INSERT INTO customer\n" +
                    "            (CUSTOMER_ID,\n" +
                    "             CUSTOMER_NAME,\n" +
                    "             POST_CODE,\n" +
                    "             FIELD_ID,\n" +
                    "             SOURCE_ID,\n" +
                    "             CREATE_TIME,\n" +
                    "             UPDATE_TIME,\n" +
                    "             EMPLOYEE_NUMBERS,\n" +
                    "             PRINPICAL,\n" +
                    "             ADDRESS,\n" +
                    "             TAG,\n" +
                    "             REMARKS,\n" +
                    "             BUSUBESS_VOLUME)\n" +
                    "VALUES (NULL,\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        NOW(),\n" +
                    "        NOW(),\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        ?,\n" +
                    "        ?)";
            Object[] params = {customer.getCustomerName(),customer.getPostCode(),customer.getField().getFieldId(),customer.getSource().getSourceId(),
            customer.getEmployeeNumbers(),customer.getPrinpical().getLinkMainId(),customer.getAddress(),customer.getTag(),customer.getRemarks(),customer.getBusubessVolume()};
            return super.jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public boolean update(Customer customer) throws DAOException {
        try {
            String sql = "UPDATE customer\n" +
                    "SET CUSTOMER_NAME = ?,\n" +
                    "  POST_CODE = ?,\n" +
                    "  FIELD_ID = ?,\n" +
                    "  SOURCE_ID = ?,\n" +
                    "  UPDATE_TIME = NOW(),\n" +
                    "  EMPLOYEE_NUMBERS = ?,\n" +
                    "  PRINPICAL = ?,\n" +
                    "  ADDRESS = ?,\n" +
                    "  TAG = ?,\n" +
                    "  REMARKS = ?,\n" +
                    "  BUSUBESS_VOLUME = ?\n" +
                    "WHERE CUSTOMER_ID = ?";
            Object[] params = {customer.getCustomerName(),customer.getPostCode(),customer.getField().getFieldId(),customer.getSource().getSourceId(),
                    customer.getEmployeeNumbers(),customer.getPrinpical().getLinkMainId(),customer.getAddress(),customer.getTag(),customer.getRemarks(),
                    customer.getBusubessVolume(),customer.getCustomerId()};
            return super.jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }

    @Override
    public List<Customer> selectByCond(Map<String, String> condMap) throws DAOException {
        try {
            String sql = "SELECT\n" +
                    "  c.CUSTOMER_ID         customerId,\n" +
                    "  c.CUSTOMER_NAME       customerName,\n" +
                    "  c.POST_CODE           postCode,\n" +
                    "  c.CREATE_TIME         createTime,\n" +
                    "  c.UPDATE_TIME         updateTime,\n" +
                    "  c.EMPLOYEE_NUMBERS    employeeNumbers,\n" +
                    "  c.ADDRESS             address,\n" +
                    "  c.TAG                 tag,\n" +
                    "  c.REMARKS             remarks,\n" +
                    "  c.BUSUBESS_VOLUME     busubessVolume,\n" +
                    "  l.LINK_MAIN_ID        \"prinpical.linkMainId\",\n" +
                    "  l.NAME                \"prinpical.name\",\n" +
                    "  l.SEX                 \"prinpical.sex\",\n" +
                    "  l.POSITION            \"prinpical.position\",\n" +
                    "  l.NICKNAME            \"prinpical.nickname\",\n" +
                    "  l.PHONENUM            \"prinpical.phonenum\",\n" +
                    "  l.EMAIL               \"prinpical.email\",\n" +
                    "  l.QQ                  \"prinpical.qq\",\n" +
                    "  l.CREATE_TIME         \"prinpical.createTime\",\n" +
                    "  l.UPDATE_TIME         \"prinpical.updateTime\",\n" +
                    "  l.REMARKS             \"prinpical.remarks\",\n" +
                    "  l.QR_CODE             \"prinpical.qrode\",\n" +
                    "  l.CUSTOMER_ID         \"prinpical.customerId\",\n" +
                    "  bs.SOURCE_ID          \"source.sourceId\",\n" +
                    "  bs.SOURCE_NAME        \"source.sourceName\",\n" +
                    "  wf.FIELD_ID           \"field.fieldId\",\n" +
                    "  wf.FIELD_NAME         \"field.fieldName\"\n" +
                    "FROM customer c\n" +
                    "  LEFT JOIN link_man l\n" +
                    "    ON c.PRINPICAL = l.LINK_MAIN_ID\n" +
                    "  LEFT JOIN business_source bs\n" +
                    "    ON c.SOURCE_ID = bs.SOURCE_ID\n" +
                    "  LEFT JOIN working_field wf\n" +
                    "    ON c.FIELD_ID = wf.FIELD_ID" +
                    " WHERE 1=1 ";
            //动态拼接SQL
            StringBuffer sb = new StringBuffer(sql);
            //条件无效  或者 没有条件
            boolean isCond = condMap == null || condMap.isEmpty();
            //创建集合 存放 动态参数值 ？ 替换的值
            List<Object> condParamValueList = new ArrayList<>();
            //有效条件
            if (!isCond) {
                String type = condMap.get("type");
                String text = condMap.get("text");
                String texTime = condMap.get("texTime");
                switch (type){
                    //客户名称 模糊查询
                    case "1":
                        sb.append("  AND c.CUSTOMER_NAME like ? ") ;
                        condParamValueList.add("%"+text+"%") ;
                        break;
                    //所属行业 的id 进行精确查询
                    case "2":
                        sb.append(" AND  wf.FIELD_ID = ? ") ;
                        condParamValueList.add(text) ;
                        break;
                    //客户来源 的id 进行精确查询
                    case "3":
                        sb.append(" AND  bs.SOURCE_ID = ? ") ;
                        condParamValueList.add(text) ;
                        break;
                    //负责人 的id 进行精确查询
                    case "4":
                        sb.append(" AND  l.LINK_MAIN_ID  = ? ") ;
                        condParamValueList.add(text) ;
                        break;
                    //创建时间 返回查询
                    case "5":
                        sb.append(" AND  c.CREATE_TIME  between ? and ? ") ;
                        condParamValueList.add(text) ;
                        condParamValueList.add(texTime) ;
                        break;
                    //修改 的id 进行精确查询
                    case "6":
                        sb.append(" AND c.UPDATE_TIME  between ? and ? ") ;
                        condParamValueList.add(text) ;
                        condParamValueList.add(texTime) ;
                        break;
                }
            }
            Object[] params = condParamValueList.toArray();
            return super.queryForList(sb.toString(), params);
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw new DAOException(e.getMessage());
        }
    }
}
